//
//  ViewController.swift
//  UsingFMDB
//
//  Created by 范伟 on 16/3/12.
//  Copyright © 2016年 FanWei. All rights reserved.
//

import UIKit

class ViewController: UIViewController {

    override func viewDidLoad() {
        super.viewDidLoad()
//        insertRecord()
//        //sql注入测试
//        injectSqlTest("APPLE', 0, 0, 0.0);DELETE FROM T_STUDENT;--")
//        preparedStatementTest(["陈乔恩', 0, 0, 0.0);DELETE FROM T_STUDENT;--", 0, 33, 1.68])
        updateStudent()
    }
    private func updateStudent() {
        let sql = "UPDATE T_STUDENT SET name = ?, age = ?, gender = ?, height = ? WHERE id = ?;"
        SQLiteManager.sharedManager.queue.inDatabase { (db) -> Void in
            do{
                try db.executeUpdate(sql, values: ["乔恩", 28, false, 1.71, 9])
            } catch {
                print("error")
            }
        }
    }
    /**
     预编译 sql，并使用executeUpdate的方式执行单条sql语句提高安全性
     
     - parameter values: <#values description#>
     */
    private func preparedStatementTest(values: [AnyObject]) {
        let sql = "INSERT INTO T_STUDENT (name, gender, age, height) VALUES (?, ?, ?, ?);"
        SQLiteManager.sharedManager.queue.inDatabase { (DBM) -> Void in
            do {
                try DBM.executeUpdate(sql, values: values)
                print("插入记录成功！最后插入的记录id为：\(DBM.lastInsertRowId())")
            } catch {
                print("error")
            }
        }
    }

    /**
     sql注入漏洞
     
     - parameter name: <#name description#>
     */
    private func injectSqlTest(name: String) {
        let sql = "INSERT INTO T_STUDENT (name, gender, age, height) VALUES ('\(name)', 1, 28, 1.71);"
        SQLiteManager.sharedManager.queue.inDatabase { (db) -> Void in
            if db.executeStatements(sql) {
                print("OK")
            } else {
                print("error")
            }
        }
    }
    
    private func insertRecord() {
        let sql = "INSERT INTO T_STUDENT (name, gender, age, height) VALUES ('Fanwei', 1, 28, 1.71);"
        SQLiteManager.sharedManager.queue.inDatabase { (db) -> Void in
            if db.executeStatements(sql) {
                print("OK")
            } else {
                print("error")
            }
        }
    }
}

